// ---------------------------------------------------------
// Dung Vu's Export class
// Copyright (C) 2007 Dung Vu. All rights reserved.
// ---------------------------------------------------------

# region Includes...

using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using System.Collections.Specialized; 

# endregion // Includes...

namespace commonV2
{
	public class ExportLibs
	{
        //private static RKLib.ExportData.Export exportLib = new RKLib.ExportData.Export("Win");
		public enum Format : int {None=0,CSV = 1, Excel = 2,XML=3 }; // Export format enumeration			

        public static void ExportToDisk(CrystalDecisions.CrystalReports.Engine.ReportClass myReport, 
                                        CrystalDecisions.Shared.ExportFormatType expFormatType,
                                        string toFileName 
            )
        {
            CrystalDecisions.Shared.ExportOptions exportOpts = new CrystalDecisions.Shared.ExportOptions();
            CrystalDecisions.Shared.DiskFileDestinationOptions diskOpts = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions();

            exportOpts.ExportFormatType = expFormatType;
            exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;

            diskOpts.DiskFileName = toFileName;
            exportOpts.ExportDestinationOptions = diskOpts;

            myReport.Export(exportOpts);
        }

        
        private static string ReplaceSpecialSQLChars(string str)
        {
            str = str.Replace('\'', ' ');
            return str;
        }
        private static OleDbConnection OpenConnection(string excelFile)
        {
            OleDbConnection oleDbConn = new OleDbConnection();
            oleDbConn.ConnectionString =
                "Data Source=" + excelFile + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";
            try
            {
                oleDbConn.Open();
                return oleDbConn;
            }
            catch { }
            return null;
        }

        private static bool CreateExcelFile(string excelFile, string sheetName, DataTable frTable,
                                            string[] columnList, string[] headerList)
        {
            string SQLCmd = "";
            OleDbConnection oleDbConn = OpenConnection(excelFile);
            if (oleDbConn == null) return false;
            try
            {
                string columnName;
                for (int idx = 0; idx < columnList.Length; idx++)
                {
                    columnName = columnList[idx];
                    SQLCmd += (SQLCmd == "" ? "" : ",") + "[" + headerList[idx] + "]";
                    switch (frTable.Columns[columnName].DataType.ToString().Trim())
                    {
                        case "System.Float":
                        case "System.Double":
                        case "System.Decimal":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                            SQLCmd += " double";
                            break;
                        case "System.DateTime":
                            SQLCmd += " date ";
                            break;
                        case "System.Boolean":
                            SQLCmd += " bit";
                            break;
                        default:
                            SQLCmd += " ntext";
                            break;
                    }
                }
                SQLCmd = "CREATE TABLE [" + sheetName + "] (" + SQLCmd + ");";
                OleDbCommand oleDbComm = new OleDbCommand(SQLCmd, oleDbConn);
                oleDbComm.ExecuteNonQuery();
                oleDbConn.Close();
                return true;
            }
            catch (Exception er)
            {
                errorHandler.lastException = er;
            }
            finally
            {
                if (oleDbConn != null && oleDbConn.State == ConnectionState.Open) oleDbConn.Close();
            }
            return false;
        }

        //public static void ExportToExcelNew(DataTable tbl, string excelFile)
        //{
        //    exportLib.ExportDetails(tbl, RKLib.ExportData.Export.ExportFormat.Excel, excelFile);
        //}


        public static bool ExportToExcel(DataTable frTable, string excelFile, string sheetName,
                                         string[] columnList, string[] headerList)
        {
            OleDbConnection oleDbConn = null;
            if (columnList == null)
            {
                columnList = new string[frTable.Columns.Count];
                for (int idx = 0; idx < frTable.Columns.Count; idx++) columnList[idx] = frTable.Columns[idx].ColumnName;
            }
            if (headerList == null)
            {
                headerList = new string[columnList.Length];
                columnList.CopyTo(headerList, 0);
            }
            // Ensure columnList.Length = headerList.Length
            int diffColNo = columnList.Length - headerList.Length;
            Array.Resize(ref headerList, columnList.Length);
            if (diffColNo > 0)
            {
                // Added headerList's items 
                for(int idx=0;idx<diffColNo;idx++)
                {
                    headerList[columnList.Length-idx-1] = "";
                }
            }

            //Change all space in headers to '-'
            for (int idx = 0; idx < headerList.Length; idx++) headerList[idx] = headerList[idx].Replace(' ', '_');

            if (File.Exists(excelFile)) File.Delete(excelFile);
            if (!CreateExcelFile(excelFile, sheetName, frTable, columnList, headerList)) return false;

            oleDbConn = OpenConnection(excelFile);
            if (oleDbConn == null) return false;

            string SQL_part1 = "";
            for (int idx = 0; idx < columnList.Length; idx++)
            {
                SQL_part1 += (SQL_part1 == "" ? "" : ",") + "[" + headerList[idx] + "]";
            }
            SQL_part1 = "INSERT INTO [" + sheetName + "](" + SQL_part1 + ")";

            DateTime dt = DateTime.Today;
            string SQL_part2, tmp;
            OleDbCommand oleDbComm = new OleDbCommand();
            oleDbComm.Connection = oleDbConn;
            string columnName;
            for (int rowId = 0; rowId < frTable.Rows.Count; rowId++)
            {
                SQL_part2 = "";
                for (int idx = 0; idx < columnList.Length; idx++)
                {
                    columnName = columnList[idx];
                    tmp = frTable.Rows[rowId][columnName].ToString().Trim();
                    switch (frTable.Columns[columnName].DataType.ToString().Trim())
                    {
                        case "System.Float":
                        case "System.Double":
                        case "System.Decimal":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                            double d = 0; double.TryParse(tmp, out d); tmp = StringLibs.Number2SqlString(d);
                            SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? tmp : "0"); ;
                            break;
                        case "System.DateTime":
                            if (DateTime.TryParse(tmp, out dt)) tmp = StringLibs.Date2SqlString(dt);
                            else tmp = "";
                            SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? "'" + tmp + "'" : "null");
                            break;
                        case "System.Boolean":
                            SQL_part2 += (SQL_part2 == "" ? "" : ",") + (tmp != "" ? tmp : "null");
                            break;
                        default:
                            tmp = ReplaceSpecialSQLChars(tmp);
                            SQL_part2 += (SQL_part2 == "" ? "" : ",") + "'" + tmp + "'";
                            break;
                    }
                }
                SQL_part2 = " VALUES (" + SQL_part2 + ")";
                oleDbComm.CommandText = SQL_part1 + SQL_part2;
                oleDbComm.ExecuteNonQuery();
            }
            oleDbConn.Close();
            return true;
        }

        public static bool ExportToExcel(DataView view, string excelFile, string sheetName,
                                         string[] columnList, string[] headerList)
        {
            return ExportToExcel(view.ToTable(), excelFile, sheetName, columnList, headerList);
        }
        public static bool ExportToExcel(DataTable frTable, string excelFile, string[] columnList, string[] headerList)
        {
            return ExportToExcel(frTable, excelFile, "Sheet1", columnList, headerList);
        }
        public static bool ExportToExcel(DataTable frTable, string excelFile, string sheetName)
        {
            return ExportToExcel(frTable, excelFile, sheetName, null, null);
        }
        public static bool ExportToExcel(DataTable frTable, string excelFile)
        {
            return ExportToExcel(frTable, excelFile, "Sheet1", null, null);
        }
	}
}
